package com.ws.shop.dao.impl;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.FileDir;
import com.ws.shop.bean.Page;
import com.ws.shop.common.CacheManager;
import com.ws.shop.dao.AuctionDao;
import com.ws.shop.entity.Auction;
import com.ws.shop.entity.AuctionLog;
import com.ws.shop.entity.Member;
import com.ws.shop.entity.Participate;
import com.ws.shop.util.DateUtil;
import com.ws.shop.util.JdbcUtil;
import com.ws.shop.util.StringUtil;
import com.ws.shop.util.file.FileUtil;
@Repository(value="auctionDao")
public class AuctionDaoImpl extends BaseDaoImpl implements AuctionDao {
	
	@Resource(name="fileDir")
	private FileDir fileDir;
	
	private static Logger log = Logger.getLogger(AuctionDaoImpl.class);
	
	String queryfiled = "a.id,a.sn,a.gid,a.goodsname,a.goodsprice,a.goods_pic,a.starttime,a.endtime,a.ordertime,a.startprice,a.addsecond,a.addprice,a.usebeat,a.pointc,a.costprice,a.recommend,a.auctioncount,a.realprice,a.auctionuname,a.uid,a.robots,a.robotspromote,a.min,a.max,a.target,a.agoid,a.agopic,a.delaytype,"
+"a.type,a.rebate,a.boutlimit,a.iskeep,a.beforebout,a.discount,a.isdiscount,a.isfree,a.maxaddsecond,a.state,a.html";
	
	public List<Map<String,Object>> findeAuctionList(final int state,final int limit){
		StringBuilder sql = new StringBuilder();
		sql.append("select " + queryfiled + ",ac.thumbnail from jvf_auction as a ")
			.append(" left join jvf_accessory as ac on a.goods_pic = ac.id where a.state =")
			.append(state).append(" order by a.endtime");
		return this.findList(0, limit, sql);
	}
	
	//获取头条
	public List<Map<String,Object>> getHead() throws SQLException{
		StringBuffer sbuf = new StringBuffer();
		sbuf.append("select " + queryfiled + ",ac.path,acc.path as agopath ");
		sbuf.append("from jvf_auction as a join jvf_accessory as ac on a.goods_pic = ac.id join jvf_accessory as acc on a.agopic = acc.id where a.state = 1 and a.recommend =1 limit 1");
		log.info(" getHead sql " + sbuf.toString());
		return this.queryForList(sbuf.toString());
	}
	
	
	public List<Auction> showAuction(String condition ){
		String s = "select " + queryfiled + " from jvf_auction as a where a.id in("+condition+")";
		log.info("show auction sql " + s);
		return this.query(s, new ResultSetExtractor<List<Auction>>(){
			public List<Auction> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Auction> auctlist = new ArrayList<Auction>();
				while(rs.next()){
					Auction a = new Auction();
					a.setId(rs.getLong(1));
					a.setSn(rs.getString(2));
					a.setGid(rs.getInt(3));
					a.setGoodsname(rs.getString(4));
					a.setGoodsprice(rs.getFloat(5));
					a.setGoods_pic(rs.getInt(6));
					a.setStarttime(rs.getTimestamp(7));
					a.setEndtime(rs.getTimestamp(8));
					a.setOrdertime(rs.getTimestamp(9));
					a.setStartprice(rs.getFloat(10));
					a.setAddsecond(rs.getInt(11));
					a.setAddprice(rs.getFloat(12));
					a.setUsebeat(rs.getInt(13));
					a.setPointc(rs.getInt(14));
					a.setCostprice(rs.getFloat(15));
					a.setRecommend(rs.getInt(16));
					a.setAuctioncount(rs.getInt(17));
					a.setRealprice(rs.getFloat(18));
					a.setAuctionuname(rs.getString(19));
					a.setUid(rs.getInt(20));
					a.setRobots(rs.getInt(21));
					a.setRobotspromote(rs.getFloat(22));
					a.setMin(rs.getInt(23));
					a.setMax(rs.getInt(24));
					a.setTarget(rs.getFloat(25));
					a.setAgoid(rs.getInt(26));
					a.setAgopic(rs.getInt(27));
					a.setDelaytype(rs.getInt(28));
					a.setType(rs.getInt(29));
					a.setRebate(rs.getInt(30));
					a.setBoutlimit(rs.getInt(31));
					a.setIskeep(rs.getInt(32));
					a.setBeforebout(rs.getInt(33));
					a.setDiscount(rs.getInt(34));
					a.setIsdiscount(rs.getInt(35));
					a.setIsfree(rs.getInt(36));
					a.setMaxaddsecond(rs.getInt(37));
					a.setState(rs.getInt(38));
					a.setHtml(rs.getString(39));
					auctlist.add(a);
				}
				return auctlist;
			}
		});
	}
	
	//查询所有某正在竞拍的或者其他状态的竞拍信息
	public Map<Long,Auction> getAuctionByState(final int state){
		return (Map)this.execute(
				new ConnectionCallback() {
					public Object doInConnection(Connection connect) {
						Statement smt = null;
						ResultSet rs = null;
						try { 
							String s = "select " + queryfiled + ",u.head from jvf_auction as a left join jvf_user as u on a.uid = u.id where a.state = " + state;
							smt = connect.createStatement();
							rs = smt.executeQuery(s);
							Map<Long,Auction> auctmap = new HashMap<Long,Auction>();
							while(rs.next()){
								Auction a = new Auction();
								a.setId(rs.getLong(1));
								a.setSn(rs.getString(2));
								a.setGid(rs.getInt(3));
								a.setGoodsname(rs.getString(4));
								a.setGoodsprice(rs.getFloat(5));
								a.setGoods_pic(rs.getInt(6));
								a.setStarttime(rs.getTimestamp(7));
								a.setEndtime(rs.getTimestamp(8));
								a.setOrdertime(rs.getTimestamp(9));
								a.setStartprice(rs.getFloat(10));
								a.setAddsecond(rs.getInt(11));
								a.setAddprice(rs.getFloat(12));
								a.setUsebeat(rs.getInt(13));
								a.setPointc(rs.getInt(14));
								a.setCostprice(rs.getFloat(15));
								a.setRecommend(rs.getInt(16));
								a.setAuctioncount(rs.getInt(17));
								a.setRealprice(rs.getFloat(18));
								a.setAuctionuname(rs.getString(19));
								a.setUid(rs.getInt(20));
								a.setRobots(rs.getInt(21));
								a.setRobotspromote(rs.getFloat(22));
								a.setMin(rs.getInt(23));
								a.setMax(rs.getInt(24));
								a.setTarget(rs.getFloat(25));
								a.setAgoid(rs.getInt(26));
								a.setAgopic(rs.getInt(27));
								a.setDelaytype(rs.getInt(28));
								a.setType(rs.getInt(29));
								a.setRebate(rs.getInt(30));
								a.setBoutlimit(rs.getInt(31));
								a.setIskeep(rs.getInt(32));
								a.setBeforebout(rs.getInt(33));
								a.setDiscount(rs.getInt(34));
								a.setIsdiscount(rs.getInt(35));
								a.setIsfree(rs.getInt(36));
								a.setMaxaddsecond(rs.getInt(37));
								a.setState(rs.getInt(38));
								a.setHtml(rs.getString(39));
								a.setUhead(rs.getString(40));
								auctmap.put(a.getId(), a);
							}
							return auctmap;
						} catch (Exception e) {
							e.printStackTrace();
							log.error("getAuctionBystate!",e);
							return null;
						} finally {
							JdbcUtil.close(rs, smt, null);
			      		}
			}
		});
	}
	
	//获取推荐
	public List<Map<String,Object>> getRecommend(String date) throws SQLException{
		StringBuffer bufer = new StringBuffer();
		bufer.append("select "+queryfiled+",ac.thumbnail from jvf_auction as a ");
		bufer.append(" left join jvf_accessory as ac on a.goods_pic = ac.id where ");
		bufer.append("a.state in(0,1) and a.recommend = 1 ");
		if ("today".equals(date)){
			bufer.append(" and to_days(a.starttime) = to_days(now()) ");
		}else if ("tomorrow".equals(date)){
		    bufer.append(" and date_add(curdate(), interval 1 day) = date(a.starttime)");
		}
		log.info("+++++ getRecommend sql ++++" + bufer.toString());
		return queryForList(bufer.toString());
	}
	
	//获取条件ID集；获取所有竞拍中的id集合
	public String getids(int state) throws SQLException{ //state 传进来为1
		String s = "select id from jvf_auction where state = " + state;
		List<Map<String,Object>> maps = this.queryForList(s);
		StringBuffer bufer = new StringBuffer();
		for(int i=0;i<maps.size();i++){
			 long v = Long.valueOf(String.valueOf(maps.get(i).get("id")));
			 bufer.append(v).append(",");
		}
	    if(bufer.length()>0){
	    	return bufer.toString().substring(0,bufer.length()-1);
	    }
		return bufer.toString();
	}
	
	/**
	 *查询竞拍日志 
	 */
	public AuctionLog findAuctionLog(final long aid,final int uid){
		 return this.query("select * from jvf_auction_log where aid=" + aid + " and uid = "+uid, new ResultSetExtractor<AuctionLog>(){
				public AuctionLog extractData(ResultSet rs) throws SQLException,
						DataAccessException {
					AuctionLog auclog = null;
					while(rs.next()){
						auclog = new AuctionLog();
						auclog.setId(rs.getInt(1));
						//.........
					}
					return auclog;
				}
		  });
	}
	
	//统计点数
	public double beatsum(final long aid,final int uid){
		String s = "select ifnull(sum(beat),0) as beatsum from jvf_auction_log where aid = ? and uid = ?";
		return (Double)this.queryForObject(s, new Object[]{aid,uid}, double.class);
	}
	
	
	//成交商品数量
	public int getAuctionGoodsTradingVolume(int state){
		String s = "select count(1) from jvf_auction where state = " + state;
		return this.queryForInt(s);
	}

	
	/**
	 * 
	 * 是否已经保价
	 * @param uid
	 * @param aid
	 * @return
	 * 
	 */
	public boolean issaveprice(final int uid,final long aid){
		return (Boolean)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							String s = "select id from jvf_insured where aid = "+aid+" and uid ="+uid;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){
								return true;
							}else{
								return false;
							}
						}catch (Exception e) {
							log.error(e.getMessage(), e);
							return false;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
				
					}
				}
		);
	}
	
	public int saveInsured(final int uid,final long aid){
		String s = "insert info jvf_insured(aid,uid) VALUES(?,?)";
		return this.update(s, new Object[]{aid,uid});
	}
	
	
	//更新开始竞拍状态
	public int updateState(long id,int state){
		return this.update("update jvf_auction set state=? where id=?",state, id);
	}

	//竞拍
   public  Auction bids(final int uid,final int aid){
	   return (Auction)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "select name,mail,password,realname,ip,head,sex,birthday,tel,score,beat,pointb,pointc,priority,robot,addtime,phone,old,uploadhead,state,acttime from jvf_user where id = " + uid + " limit 1 for update ";
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							Auction auct = new Auction();
						    if(rs.next()){//用户查询
						    	 Member member = new Member();
						    	 member.setId(uid);
						    	 member.setName(rs.getString(1));
						    	 member.setMail(rs.getString(2));
						    	 member.setPassword(rs.getString(3));
						    	 member.setRealname(rs.getString(4));
						    	 member.setIp(rs.getString(5));
						    	 member.setHead(rs.getString(6));
						    	 member.setSex(rs.getInt(7));
						    	 member.setBirthday(rs.getTimestamp(8));
						    	 member.setTel(rs.getString(9));
						    	 member.setScore(rs.getInt(10));
						    	 member.setBeat(rs.getInt(11));
						    	 member.setPointb(rs.getLong(12));
						    	 member.setPointc(rs.getInt(13));
						    	 member.setPriority(rs.getInt(14));
						    	 member.setRobot(rs.getInt(15));
						    	 member.setAddtime(rs.getTimestamp(16));
						    	 member.setPhone(rs.getString(17));
						    	 member.setOld(rs.getInt(18));
						    	 member.setUploadhead(rs.getInt(19));
						    	 member.setState(rs.getInt(20));
						    	 member.setActtime(rs.getTimestamp(21));
						    	 Date acttime = member.getActtime();
						    	 int old = rs.getInt(14);
						    	 if(acttime == null){//未激活
						    		 connect.rollback();
						    		 auct.setBidstate(2);
						    		 return auct;
						    	 }
						    	 if(member.getState() != 1){
						    		 connect.rollback();
						    		 auct.setBidstate(8);
						    		 return auct;
						    	 }
						    	 CacheManager cache = new CacheManager(member);
						    	 Auction auction = cache.bidAuction(new Long(aid));
						    	 if(auction.getBidstate() == 0){
						    		 //并且更新,金额
						    		 int c = updatePoint(connect,auction,uid);
						    		 if(c>0){
						    			 //写数据库文件
						    			//"uid|aid|name|goodsname|beat|pointb|ip|auctioncount|currentprice|currenttime|";
						    			 StringBuffer buffer = new StringBuffer();
						    			 buffer.append(auction.getUid()).append("|");
						    			 buffer.append(auction.getId()).append("|");
						    			 buffer.append(auction.getAuctionuname()).append("|");
						    			 buffer.append(auction.getGoodsname()).append("|");
						    			 buffer.append(auction.getBeat()).append("|");
						    			 buffer.append(auction.getPointb()).append("|");
						    			 buffer.append(member.getIp()).append("|");
						    			 buffer.append(auction.getAuctioncount()).append("|");
						    			 buffer.append(auction.getRealprice()).append("|");
						    			 buffer.append(auction.getEndtime());
						    			 String frex = DateUtil.dateToString(new Date(), DateUtil.YYMMDDHHMMSSSSS);
						    			 String strcode = frex + auction.getUid() +StringUtil.getSeq();
						    			 String fileName = fileDir.tmp + File.separator + strcode;
						    			 FileUtil.saveDataToFile(fileName, buffer.toString(), false);
						    			 String destFile = fileDir.biddir + File.separator + strcode;
						    			 FileUtil.move(fileName, destFile);
						    			 connect.commit();
						    		 }else{
						    			 auct.setBidstate(9);
						    			 connect.rollback();
						    			 return auct;
						    		 }
						    	 }else{
						    		 connect.rollback();
						    		 auct.setBidstate(auction.getBidstate());
						    		 return auct;
						    	 }
						    	 return auction; 
						    }else{
						    	connect.rollback();
						    	//不存在用户
						    	auct.setBidstate(1);
					    		return auct;
						    }
						}catch (Exception e) {
							log.error(e.getMessage(), e);
							connect.rollback();
							return null;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
   }
   
   //更新拍点
   public int updatePoint(Connection connect,Auction auction,int uid) throws SQLException{
	   String s = "update jvf_user set beat = beat - ?  where id = ?";
	   if(auction.getIsfree() == 1){
		   s = "update jvf_user set pointb = pointb - ?  where id = ?";   
	   }
	   PreparedStatement psmt = connect.prepareStatement(s);
	   psmt.setInt(1, auction.getUsebeat());
	   psmt.setInt(2, uid);
	   int c = psmt.executeUpdate();
	   return c;
   }
   
	
	public List<AuctionLog> getLogData(Connection connect,long aid) throws SQLException{
		String s = "select uid,name,ip,address,bidprice,addtime from jvf_auction_log where  aid = ?  order by id desc limit 10";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, aid);
		ResultSet rs = psmt.executeQuery();
		List<AuctionLog> list = new ArrayList<AuctionLog>();
		while (rs.next()) {
			AuctionLog alog = new AuctionLog();
			alog.setUid(rs.getInt(1));
			alog.setName(rs.getString(2));
			alog.setIp(rs.getString(3));
			alog.setAddress(rs.getString(4));
			alog.setBidprice(rs.getFloat(5));
			alog.setAddtime(rs.getTimestamp(6));
			list.add(alog);
		}
		JdbcUtil.close(rs, psmt, null);
		return list;
	}
	
	
	public Participate participate(Connection connect,long aid,int uid) throws SQLException{
		String s = "select id,uid,aid from jvf_participate where uid = ? and  aid = ?";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, uid);
		psmt.setLong(2, aid);
		ResultSet rs = psmt.executeQuery();
		Participate p = null;
		if(rs.next()){
			p = new Participate();
			p.setId(rs.getLong(1));
			p.setUid(rs.getInt(2));
			p.setAid(rs.getLong(3));
		}else{
			JdbcUtil.close(rs, psmt, null);
			s = "insert into  jvf_participate(uid, aid) values (?,?)";
			psmt = connect.prepareStatement(s);
			psmt.setLong(1, uid);
			psmt.setLong(2, aid);
			int c = psmt.executeUpdate();
		}
		JdbcUtil.close(rs, psmt, null);
		return p;
	}
	
	@SuppressWarnings("unchecked")
	public int updateAuctionState(final Auction auction){
		return(Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						PreparedStatement psmt = null;
						try{
							 String s = "update jvf_auction set uid = ?,auctionuname=?,auctioncount = ?, realprice = ?, endtime = ? where id = ?";
					         psmt = connect.prepareStatement(s);
					         psmt.setLong(1,auction.getId());
					         psmt.setString(2, auction.getAuctionuname());
					         psmt.setInt(3,auction.getAuctioncount());
					         psmt.setDouble(4, auction.getRealprice());
					         psmt.setTimestamp(5, new Timestamp(auction.getEndtime().getTime()));
					         psmt.setLong(6, auction.getId());
					         int c = psmt.executeUpdate();
					         return c;
						}catch (Exception e) {
							log.error(e.getMessage(), e);
							return -1;
						} finally {
							JdbcUtil.close(null, psmt, null);
						}
					}
				}
		);
	}
	
	public long getAuctionCount(Connection connect,int uid,long aid) throws SQLException{
		String s = "select count(1) as count from jvf_auction_log where aid = ? and uid = ? ";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, aid);
		psmt.setLong(2, uid);
		ResultSet rs = psmt.executeQuery();
		long bount = 0;
		if(rs.next()){
			bount = rs.getLong(1);
		}
		JdbcUtil.close(rs, psmt, null);
		return bount;
	}
	
	
	public Auction get(Connection connect,long aid) throws SQLException{
		String s = "select "+queryfiled+",u.head from jvf_auction as a left join  jvf_user as u on a.uid = u.id where a.id =  ?";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, aid);
		ResultSet rs = psmt.executeQuery();
		Auction a = null;
		if(rs.next()){
			a = new Auction();
			a.setId(rs.getLong(1));
			a.setSn(rs.getString(2));
			a.setGid(rs.getInt(3));
			a.setGoodsname(rs.getString(4));
			a.setGoodsprice(rs.getFloat(5));
			a.setGoods_pic(rs.getInt(6));
			a.setStarttime(rs.getTimestamp(7));
			a.setEndtime(rs.getTimestamp(8));
			a.setOrdertime(rs.getTimestamp(9));
			a.setStartprice(rs.getFloat(10));
			a.setAddsecond(rs.getInt(11));
			a.setAddprice(rs.getFloat(12));
			a.setUsebeat(rs.getInt(13));
			a.setPointc(rs.getInt(14));
			a.setCostprice(rs.getFloat(15));
			a.setRecommend(rs.getInt(16));
			a.setAuctioncount(rs.getInt(17));
			a.setRealprice(rs.getFloat(18));
			a.setAuctionuname(rs.getString(19));
			a.setUid(rs.getInt(20));
			a.setRobots(rs.getInt(21));
			a.setRobotspromote(rs.getFloat(22));
			a.setMin(rs.getInt(23));
			a.setMax(rs.getInt(24));
			a.setTarget(rs.getFloat(25));
			a.setAgoid(rs.getInt(26));
			a.setAgopic(rs.getInt(27));
			a.setDelaytype(rs.getInt(28));
			a.setType(rs.getInt(29));
			a.setRebate(rs.getInt(30));
			a.setBoutlimit(rs.getInt(31));
			a.setIskeep(rs.getInt(32));
			a.setBeforebout(rs.getInt(33));
			a.setDiscount(rs.getInt(34));
			a.setIsdiscount(rs.getInt(35));
			a.setIsfree(rs.getInt(36));
			a.setMaxaddsecond(rs.getInt(37));
			a.setState(rs.getInt(38));
			a.setHtml(rs.getString(39));
			a.setUhead(rs.getString(40));
		}
		JdbcUtil.close(rs, psmt, null);

		return a;
	}
	

	
	//机器人
	public Auction robot(Connection connect,Auction auction) throws SQLException{
		List<Member> users = this.getRobotsUser(connect, auction.getId());
		Member member = null;
		if(users.size() >= 3){
			do {
				int index = new java.util.Random().nextInt(users.size());
				member = users.get(index);
			}while (member.getId() == auction.getUid());
		}else{
			return null;
		}
		String sql = "select id,uid,aid from jvf_participate  where aid = ? and uid = ? for update ";
		PreparedStatement psmt = connect.prepareStatement(sql);
		psmt.setLong(1, auction.getId());
		psmt.setLong(2, member.getId());
		ResultSet rs = psmt.executeQuery();
		boolean isparticipate = false;
		if(rs.next()){
			isparticipate = true;
		}else{
			isparticipate = false;
		}
		JdbcUtil.close(rs, psmt, null);
		if (!isparticipate){
			sql = "insert int jvf_participate (id, uid, aid) values (?, ?)";
			psmt = connect.prepareStatement(sql);
			psmt.setLong(1, member.getId());
			psmt.setLong(2, auction.getId());
			psmt.executeUpdate();
			JdbcUtil.close(rs, psmt, null);
		}
		
		float currentprice = auction.getRealprice() + auction.getAddprice(); //当前价格
		float robotspromote = auction.getRobotspromote() + auction.getAddprice();

		Statement stmt = connect.createStatement();
	    sql = "insert into jvf_auction_log ( aid, uid, name, goodsname, beat, pointb, ip, address, bidprice, addtime) values("+auction.getId()
	    +","+member.getId()+",'"+member.getName()+"','"+auction.getGoodsname()+"',0,"+auction.getUsebeat()+",'"+member.getIp()+"','"+member.getIp()+"',"+currentprice+",now())";
	    stmt.addBatch(sql);

		//对竞拍内容的更新

	    long currenttime = 0;
	    long time = new Date().getTime();
		if(auction.getMaxaddsecond()>0){
			long diftime = auction.getEndtime().getTime()-time;
			if(diftime < auction.getMaxaddsecond()){
				currenttime = auction.getEndtime().getTime() + auction.getAddsecond(); //当前时间
				if(currenttime-time > auction.getMaxaddsecond())currenttime= time+ auction.getMaxaddsecond();
			}else{
				currenttime = auction.getEndtime().getTime();
			}
		}else{
			currenttime = auction.getEndtime().getTime() + auction.getAddsecond(); //当前时间
			if(currenttime<time){currenttime = time + auction.getAddsecond(); }
		}
		int auctioncount = auction.getAuctioncount()+1;    //对数据库的竞拍数据更新
		sql = "update jvf_auction set uid = "+member.getId()+" ,auctionuname='"+member.getName()+"',auctioncount = "+auctioncount+", realprice = "+currentprice+", endtime = "+currenttime + ",robotspromote = " + robotspromote + " where id = "+auction.getId();
        stmt.addBatch(sql);
        stmt.executeBatch();
        JdbcUtil.close(null, stmt, null);
		auction.setUid(member.getId());
		auction.setRealprice(currentprice);
		auction.setAuctionuname(member.getName());
		auction.setAuctioncount(auctioncount);
		auction.setEndtime(new Date(currenttime));
		auction.setRobotspromote(robotspromote);
		auction.setUhead(member.getHead());
		//更新缓存中的内容

		return auction;
	}
	
	//获取机器人
	public List<Member> getRobotsUser(Connection connect,long aid) throws SQLException{
		  String s = "select u.id,u.name,u.mail,u.password,u.realname,u.ip,u.head,u.sex,u.birthday,u.tel,u.score,u.beat,u.pointb,u.pointc,u.priority,u.robot,u.addtime,u.phone,u.old,u.uploadhead,u.state from jvf_robot as r left join  jvf_user as u on r.uid = u.id where r.aid = ?";
		  PreparedStatement psmt=connect.prepareStatement(s);
		  ResultSet rs = psmt.executeQuery();
		  List<Member> members = new ArrayList<Member>();
		  while(rs.next()){
			Member user = new Member();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setMail(rs.getString(3));
			user.setPassword(rs.getString(4));
			user.setRealname(rs.getString(5));
			user.setIp(rs.getString(6));
			user.setHead(rs.getString(7));
			user.setSex(rs.getInt(8));
			user.setBirthday(rs.getTimestamp(9));
			user.setTel(rs.getString(10));
			user.setScore(rs.getInt(11));
			user.setBeat(rs.getInt(12));
			user.setPointb(rs.getLong(13));
			user.setPointc(rs.getInt(14));
			user.setPriority(rs.getInt(15));
			user.setRobot(rs.getInt(16));
			user.setAddtime(rs.getTimestamp(17));
			user.setPhone(rs.getString(18));
			user.setOld(rs.getInt(19));
			user.setUploadhead(rs.getInt(20));
			user.setState(rs.getInt(21));
			members.add(user);
		  }
		  JdbcUtil.close(rs, psmt, null);
		  return members;
	}
	
	public void userAuctioningList(final Page page,final int uid){
		this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							String s = "select count(1) as c from jvf_auction as a  join jvf_participate as p on p.uid = a.uid and p.aid = a.id where p.uid = " + uid;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							long totalnum = 0;
							if (rs.next()) {
								totalnum = rs.getLong(1);
								page.setTotalnum(totalnum);
								log.info(" userAuctioningList count " + totalnum + " sql: " +s );
							}
							JdbcUtil.close(rs,stmt,null);
							if(totalnum>0){
								s = "select a.id,a.sn,a.goodsname,a.goodsprice,a.realprice,a.auctionuname,a.usebeat,a.isfree,ac.path,ac.thumbnail,a.html from jvf_auction as a join jvf_accessory ac on ac.id = a.goods_pic join jvf_participate as p on p.uid = a.uid and p.aid = a.id where p.uid = ? limit ?,?";
								PreparedStatement psmt = connect.prepareStatement(s);
								psmt.setLong(1, uid);
								psmt.setLong(2, page.getStartrownum());
								psmt.setInt(3, page.getPagesize());
								rs = psmt.executeQuery();
								while(rs.next()){
									Auction auction = new Auction();
									auction.setId(rs.getInt(1));
									auction.setSn(rs.getString(2));
									auction.setGoodsname(rs.getString(3));
									auction.setGoodsprice(rs.getFloat(4));
									auction.setRealprice(rs.getFloat(5));
									auction.setAuctionuname(rs.getString(6));
									auction.setUsebeat(rs.getInt(7));
									auction.setIsfree(rs.getInt(8));
									auction.setPath(rs.getString(9));
									auction.setThumbnail(rs.getString(10));
									auction.setHtml(rs.getString(11));
									auction.setAuctioncount(auctioncount(connect,auction.getId(),uid));
									page.addData(auction);
								}
								JdbcUtil.close(rs, psmt, null);
							}
						}catch (Exception e) {
							log.error(e.getMessage(), e);
							return null;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
						return null;
					}
				}
		);
	}
	
	public int auctioncount(Connection connect,long aid,int uid) throws SQLException{
		String s = "select count(1) from jvf_auction_log where aid = ? and uid = ? ";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, aid);
		psmt.setLong(2, uid);
		int count = 0;
		ResultSet rs = psmt.executeQuery();
		if(rs.next()){
			count = rs.getInt(1);
		}
		JdbcUtil.close(rs, psmt, null);
		return count;
	}
	
	
	final String findAuctionById = "select sn,gid,goodsname,goodsprice,goods_pic,starttime,endtime,ordertime,startprice,addsecond,addprice,usebeat,pointc,costprice,recommend,auctioncount,realprice,auctionuname,uid,robots,robotspromote,min,max,target,agoid,agopic,delaytype,type,rebate,boutlimit,iskeep,beforebout,discount,isdiscount,isfree,maxaddsecond,state,html from jvf_auction where id=?";
	public Auction findAuctionById(final int id) {
		return this.query(findAuctionById, new ResultSetExtractor<Auction>(){
			public Auction extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(!rs.next())
					return null;
				Auction a = new Auction();
				a.setId(id);
				a.setSn(rs.getString(1));
				a.setGid(rs.getInt(2));
				a.setGoodsname(rs.getString(3));
				a.setGoodsprice(rs.getFloat(4));
				a.setGoods_pic(rs.getInt(5));
				a.setStarttime(rs.getTimestamp(6));
				a.setEndtime(rs.getTimestamp(7));
				a.setOrdertime(rs.getTimestamp(8));
				a.setStartprice(rs.getFloat(9));
				a.setAddsecond(rs.getInt(10));
				a.setAddprice(rs.getFloat(11));
				a.setUsebeat(rs.getInt(12));
				a.setPointc(rs.getInt(13));
				a.setCostprice(rs.getFloat(14));
				a.setRecommend(rs.getInt(15));
				a.setAuctioncount(rs.getInt(16));
				a.setRealprice(rs.getFloat(17));
				a.setAuctionuname(rs.getString(18));
				a.setUid(rs.getInt(19));
				a.setRobots(rs.getInt(20));
				a.setRobotspromote(rs.getFloat(21));
				a.setMin(rs.getInt(22));
				a.setMax(rs.getInt(23));
				a.setTarget(rs.getFloat(24));
				a.setAgoid(rs.getInt(25));
				a.setAgopic(rs.getInt(26));
				a.setDelaytype(rs.getInt(27));
				a.setType(rs.getInt(28));
				a.setRebate(rs.getInt(29));
				a.setBoutlimit(rs.getInt(30));
				a.setIskeep(rs.getInt(31));
				a.setBeforebout(rs.getInt(32));
				a.setDiscount(rs.getInt(33));
				a.setIsdiscount(rs.getInt(34));
				a.setIsfree(rs.getInt(35));
				a.setMaxaddsecond(rs.getInt(36));
				a.setState(rs.getInt(37));
				a.setHtml(rs.getString(38));
				return a;
			}
		}, id);
	}

	final String findAuctionListPage_count = "select count(1) from jvf_auction a";
	final String findAuctionListPage_list = "select a.id,a.sn,a.gid,a.goodsname,a.goodsprice,a.starttime,a.addsecond,a.addprice,a.usebeat,a.realprice,a.auctionuname,a.endtime,a.state,ac.thumbnail,a.html from jvf_auction a left join jvf_accessory ac on a.goods_pic=ac.id";
	final String findAuctionListPage_order = " order by a.id desc";
	public Page findAuctionListPage(int pageno, int pagesize, String goodsname) {
		StringBuilder query = new StringBuilder();
		if(!StringUtil.isEmpty(goodsname))
			query.append(" where a.goodsname like '%").append(goodsname).append("%'");
		return findPage(pageno,pagesize,new StringBuilder(findAuctionListPage_count).append(query).toString()
				,new StringBuilder(findAuctionListPage_list).append(query).append(findAuctionListPage_order));
	}

	final String save_sql = "insert into jvf_auction(sn,gid,goodsname,goodsprice,goods_pic,starttime,endtime,startprice,addsecond,addprice,usebeat,pointc,costprice,recommend,realprice,robots,delaytype,type,rebate,boutlimit,iskeep,beforebout,discount,isdiscount,isfree,maxaddsecond) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	final String update_sql = "update jvf_auction set sn=?,gid=?,goodsname=?,goodsprice=?,goods_pic=?,starttime=?,endtime=?,startprice=?,addsecond=?,addprice=?,usebeat=?,pointc=?,costprice=?,recommend=?,realprice=?,robots=?,delaytype=?,type=?,rebate=?,boutlimit=?,iskeep=?,beforebout=?,discount=?,isdiscount=?,isfree=?,maxaddsecond=? where id=?";
	public void saveOrUpdate(final Auction auction) {
		if(auction.getId() == 0){
			this.update(save_sql, auction.getSn(),auction.getGid(),auction.getGoodsname(),auction.getGoodsprice(),auction.getGoods_pic(),auction.getStarttime(),auction.getEndtime(),
					auction.getStartprice(),auction.getAddsecond(),auction.getAddprice(),auction.getUsebeat(),auction.getPointc(),auction.getCostprice(),
					auction.getRecommend(),auction.getRealprice(),auction.getRobots(),auction.getDelaytype(),auction.getType(),auction.getRebate(),
					auction.getBoutlimit(),auction.getIskeep(),auction.getBeforebout(),auction.getDiscount(),auction.getIsdiscount(),auction.getIsfree(),auction.getMaxaddsecond());
		}else{
			this.update(update_sql, auction.getSn(),auction.getGid(),auction.getGoodsname(),auction.getGoodsprice(),auction.getGoods_pic(),auction.getStarttime(),auction.getEndtime(),
					auction.getStartprice(),auction.getAddsecond(),auction.getAddprice(),auction.getUsebeat(),auction.getPointc(),auction.getCostprice(),
					auction.getRecommend(),auction.getRealprice(),auction.getRobots(),auction.getDelaytype(),auction.getType(),auction.getRebate(),
					auction.getBoutlimit(),auction.getIskeep(),auction.getBeforebout(),auction.getDiscount(),auction.getIsdiscount(),auction.getIsfree(),auction.getMaxaddsecond(),auction.getId());
		}
	}

	final String findAuctionListPage_count2 = "select count(1) from jvf_auction a";
	final String findAuctionListPage_list2 = "select a.id,a.sn,a.gid,a.goodsname,a.goodsprice,a.starttime,a.endtime,a.ordertime,a.realprice,a.auctionuname,ac.thumbnail,a.html from jvf_auction a left join jvf_accessory ac on a.goods_pic=ac.id";
	final String findAuctionListPage_order2 = " order by a.id desc";
	public Page findAuctionListPage(int pageno, int pagesize, int cid,int state,int isfree) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(isfree >= 0){
			query.append(" and a.isfree=").append(isfree);
		}
		if(state >= 0){
			query.append(" and a.state=").append(state);
		}
		if(cid > 0){
			query.append(" and exists(select 1 from jvf_goods where a.gid=id and cid=").append(cid).append(")");
		}
		return findPage(pageno,pagesize,new StringBuilder(findAuctionListPage_count2).append(query).toString()
				,new StringBuilder(findAuctionListPage_list2).append(query).append(findAuctionListPage_order2));
	}


	final String findAuctionNeedStart = "select id from jvf_auction where starttime <= now() and endtime > now() and state = 0";
	public List<Map<String, Object>> findAuctionNeedStart() {
		return this.queryForList(findAuctionNeedStart);
	}

	final String findRobotsByAid = "select u.id,u.name from jvf_user u,jvf_robot r where u.id=r.uid and aid=? order by u.id";
	public List<Map<String, Object>> findRobotsByAid(int aid) {
		return this.queryForList(findRobotsByAid, aid);
	}

	final String findSelectRobotsByAidPage_count = "select count(1) from jvf_user u left join (select id,uid from jvf_robot where aid=?) r on u.id=r.uid where u.robot=1";
	final String findSelectRobotsByAidPage_list = "select u.id,u.name,u.beat,u.pointb,u.state,r.id rid from jvf_user u left join (select * from jvf_robot where aid=?) r on u.id=r.uid where u.robot=1";
	final String findSelectRobotsByAidPage_order = " order by r.id desc";
	public Page findSelectRobotsByAidPage(int pageno, int pagesize, int aid) {
		return this.findPage(pageno, pagesize,findSelectRobotsByAidPage_count,
				new StringBuilder(findSelectRobotsByAidPage_list).append(findSelectRobotsByAidPage_order),aid);
	}

	final String addauctionrobot = "insert into jvf_robot(aid,uid) values(?,?)";
	public void addauctionrobot(int aid, int uid) {
		this.update(addauctionrobot, aid,uid);
	}

	final String delauctionrobot = "delete from jvf_robot where id=?";
	public void delauctionrobot(int rid) {
		this.update(delauctionrobot, rid);
	}

	final String findAuctionList_list = "select a.id,a.sn,a.gid,a.goodsname,a.goodsprice,a.starttime,ac.thumbnail,a.html from jvf_auction a left join jvf_accessory ac on a.goods_pic=ac.id where a.state=? order by a.starttime";
	public List<Map<String, Object>> findAuctionList(int startrow,
			int limit,int state) {
		return this.findList(startrow, limit, new StringBuilder(findAuctionList_list),state);
	}

	final String updateHtml = "update jvf_auction set html=? where id=?";
	public void updateHtml(long id, String htmlFilePath) {
		this.update(updateHtml,htmlFilePath,id);
	}

}
